package com.xinchuang.mapper.targetSystem;

import com.xinchuang.entity.targetSystem.IdeaData;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @ClassName IdeaMapper
 * @Description: TODO(想定)
 * @Author zhangxuan
 * @Date 2022-09-05 15:57
 * @Verion V1.0
 */
@Repository
public interface IdeaMapper {

    @Select(value = {
            "SELECT a.target_id AS targetId,a.target_name AS targetName,c.score,b.stage FROM ",
            "(SELECT td.target_id,td.target_name FROM system_relation sr ",
            "JOIN target_base_data td ON sr.target_id = td.target_id ",
            "WHERE sr.s_id = #{systemId}) AS a ",
            "LEFT JOIN ",
            "(SELECT target_id,stage FROM idea_table WHERE system_id = #{systemId}) AS b ",
            "ON ",
            "a.target_id = b.target_id ",
            "LEFT JOIN ",
            "(SELECT target_id,score FROM value_table WHERE rel_id = #{systemId} AND target_id IS NOT NULL AND sub_id IS NULL) AS c ",
            "ON ",
            "a.target_id = c.target_id "
    })
    List<Map<String, Object>> selectIdeaSystemTargetList(@Param(value = "systemId") String systemId);

    @Select(value = {
            "SELECT a.target_id AS targetId,a.target_name AS targetName,c.score,b.stage FROM ",
            "(SELECT td.target_id,td.target_name FROM systems_relation sr ",
            "JOIN target_base_data td ON sr.target_id = td.target_id ",
            "WHERE sr.sys_id = #{systemsId}) AS a ",
            "LEFT JOIN ",
            "(SELECT target_id,stage FROM idea_table WHERE system_id = #{systemsId}) AS b ",
            "ON ",
            "a.target_id = b.target_id ",
            "LEFT JOIN ",
            "(SELECT target_id,score FROM value_table WHERE rel_id = #{systemsId} AND target_id IS NOT NULL AND sub_id IS NULL) AS c ",
            "ON ",
            "a.target_id = c.target_id "
    })
    List<Map<String, Object>> selectIdeaSystemsTargetList(@Param(value = "systemsId") String systemId);

    @Select(value = "SELECT stage FROM idea_table WHERE system_id = #{systemsId} GROUP BY stage")
    List<Integer> selectIdeaStage(@Param(value = "systemsId") String systemId);

    @Select(value = "SELECT td.target_name FROM target_base_data td JOIN idea_table it ON td.target_id = it.target_id WHERE it.system_id = #{systemsId} AND it.stage = #{stage}")
    List<String> selectIdeaStageList(@Param("systemsId") String systemId, @Param("stage") Integer stage);

    @Select(value = "SELECT SUM(score) FROM value_table WHERE rel_id = #{systemsId} AND target_id IS NOT NULL AND sub_id IS NULL")
    Double selectIdeaValueSum(@Param("systemsId") String systemId);

    @Select(value = {
            "SELECT SUM(score) FROM value_table ",
            "WHERE rel_id = #{systemsId} ",
            "AND target_id IN (SELECT target_id FROM idea_table WHERE system_id = #{systemsId} AND stage = #{stage}) ",
            "AND sub_id IS NULL"
    })
    Double selectIdeaStageValueSum(@Param("systemsId") String systemId, @Param("stage") Integer stage);

    @Delete(value = "DELETE FROM idea_table WHERE system_id = #{systemsId}")
    int clearIdea(@Param("systemsId") String systemId);

    @Delete(value = "DELETE FROM idea_table WHERE system_id = #{systemsId} AND stage = #{stage}")
    int deleteIdeaStage(@Param("systemsId") String systemId, @Param("stage") Integer stage);

    @Insert(value = {
            "<script>",
            "INSERT INTO idea_table (id,system_id,target_id,stage,remark) VALUES ",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "(#{item.id},#{item.systemId},#{item.targetId},#{item.stage},#{item.remark})",
            "</foreach>",
            "</script>"
    })
    int insertIdeaStage(@Param("list") List<IdeaData> list);

    @Update(value = "UPDATE idea_table SET stage = #{updateStage} WHERE system_id = #{systemsId} AND stage = #{stage}")
    int updateStageBySystemIdAndStage(@Param("systemsId") String systemId, @Param("stage") Integer stage, @Param("updateStage") Integer updateStage);

    @Delete(value = {
            "<script>",
            "DELETE FROM idea_table WHERE system_id = #{systemId} AND target_id IN (",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "#{item}",
            "</foreach>",
            ")",
            "</script>"
    })
    int deleteIdeaTargetList(@Param("systemId") String systemId, @Param("list") List<String> targetList);

    @Delete(value = "DELETE FROM idea_table WHERE target_id = #{targetId}")
    int deleteIdeaTarget(@Param("targetId") String targetId);

}
